package DataAccess;

import DomainModel.*;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
/**
 *
 * @author Reynier
 * @date 11/10/2013
 *
 */
public class SectionDA
{
    private DBManager dbm;
    private Connection conn;
    private PreparedStatement prep;

    public SectionDA() throws Exception {
        conn = DBManager.getInstance().getConnection();
    }

    public List<Section> getProfessorSections(int professorId) throws Exception {
        List<Section> sections = new LinkedList<>();

        String query = "select s.id, s.course_id, s.name as sectionName, s.semester_id as semesterId, \n"
                + "s.semester_year as semesterYear, c.code as courseCode, c.name as courseName, c.credit_hours as creditHours, \n"
              + "	s.professor_id, u.first_name as profFirstName, u.last_name as profLastName, u.email as profEmail, u.user_name as profUsername \n"
              + "from sections s \n"
              + "join users u on u.id = s.professor_id \n"
              + "join courses c on c.id = s.course_id \n"
              + "where s.professor_id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, professorId);

        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                sections.add(this.createSection(rs));
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }

        return sections;
    }

    public List<Section> getStudentSections(int studentId) throws Exception {
        List<Section> sections = new LinkedList<>();

        String query = "select s.id, s.course_id, s.name as sectionName, s.semester_id as semesterId, s.semester_year as semesterYear, c.code as courseCode, c.name as courseName, c.credit_hours as creditHours, \n"
              + "	s.professor_id, u.first_name as profFirstName, u.last_name as profLastName, u.email as profEmail, u.user_name as profUsername \n"
              + "from sections s \n"
              + "join users u on u.id = s.professor_id \n"
              + "join courses c on c.id = s.course_id \n"
              + "join students_sections ss on ss.section_id = s.id \n"
              + "where ss.student_id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, studentId);

        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                sections.add(this.createSection(rs));
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }

        return sections;
    }
    
    List<User> getSectionStudents(int sectionId) throws Exception {
        List<User> sectionStudentList = new LinkedList<>();
        String query = "select student_id from students_sections where section_id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, sectionId);
        ResultSet rs = prep.executeQuery();       
   
        try {
            if(rs.next()) { //if the user was found
                User user = DataAccessFacade.getInstance().getUserById(rs.getInt("student_id"));
                sectionStudentList.add(user);
            }
        }
        catch (Exception e) {
            System.out.println("getSectionStudents: Error while fetching students: "+e.getMessage());
            throw e;
        }
        finally {
            prep.close();
        }
        return sectionStudentList;
    }
    
    void setSectionStudents(int sectionId, List<Integer> studentIdList) throws SQLException {
        String query = "insert into students_sections (student_id, section_id) values(?, ?)";
        prep = conn.prepareStatement(query);
        prep.setInt(2, sectionId);
        for(Integer studentId : studentIdList) {            
            prep.setInt(1, studentId);
            prep.executeUpdate();
        }
        prep.close();
    }
    
    public Section getSectionByIdAndUser(int sectionId, int userId) throws Exception {
        String query = "select s.id, s.course_id, s.name as sectionName, s.semester_id as semesterId, s.semester_year as semesterYear, c.code as courseCode, c.name as courseName, c.credit_hours as creditHours, \n"
              + "	s.professor_id, u.first_name as profFirstName, u.last_name as profLastName, u.email as profEmail, u.user_name as profUsername \n"
              + "from sections s \n"
              + "join users u on u.id = s.professor_id \n"
              + "join courses c on c.id = s.course_id \n"
              + "join students_sections ss on ss.section_id = s.id \n"
              + "where s.id = ? and (ss.student_id = ? or s.professor_id = ?)";
        prep = conn.prepareStatement(query);
        prep.setInt(1, sectionId);
        prep.setInt(2, userId);
        prep.setInt(3, userId);

        Section section = null;
        try (ResultSet rs = prep.executeQuery()) {
            if (rs.next()) {
                section = this.createSection(rs);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }

        return section;
    }
    
     public Section getSectionByIdAndProf(int sectionId, int userId) throws Exception {
        String query = "select s.id, s.course_id, s.name as sectionName, s.semester_id as semesterId, \n"
                + "s.semester_year as semesterYear, c.code as courseCode, c.name as courseName, c.credit_hours as creditHours, \n"
              + "	s.professor_id, u.first_name as profFirstName, u.last_name as profLastName, u.email as profEmail, u.user_name as profUsername \n"
              + "from sections s \n"
              + "join users u on u.id = s.professor_id \n"
              + "join courses c on c.id = s.course_id \n"
              + "where s.id = ? and (s.professor_id = ?)";
        prep = conn.prepareStatement(query);
        prep.setInt(1, sectionId);
        prep.setInt(2, userId);
        
        Section section = null;
        try (ResultSet rs = prep.executeQuery()) {
            if (rs.next()) {
                section = this.createSection(rs);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }

        return section;
    }
    

    public void setSection(Section section) throws SQLException {
        String q;
        if (section.getSectionID() == 0) {
            // INSERT new section
            q = "INSERT INTO sections (name, professor_id, course_id, semester_id, semester_year) "
                  + "VALUES (?, ?, ?, ?, ?)";
            prep = conn.prepareStatement(q);
            prep.setString(1, section.getSectionName());
            prep.setInt(2, section.getProfessor().getUserId());
            prep.setInt(3, section.getCourse().getCourseId());
            prep.setInt(4, section.getSemesterType().getSemesterTypeId());
            prep.setInt(5, section.getSemesterYear());
        } else {
            // UPDATE section
            q = "UPDATE sections "
                  + "SET name = ?, professor_id = ?, course_id = ?, semester_id = ?, semester_year = ? WHERE id = ?";
            prep = conn.prepareStatement(q);
            prep.setString(1, section.getSectionName());
            prep.setInt(2, section.getProfessor().getUserId());
            prep.setInt(3, section.getCourse().getCourseId());
            prep.setInt(4, section.getSemesterType().getSemesterTypeId());
            prep.setInt(5, section.getSemesterYear());
            prep.setInt(6, section.getSectionID());
        }

        try {
            prep.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Error while setting section: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }
    }

    public List<Section> getAllSections() throws Exception {
        List<Section> allSections = new LinkedList<>();
        String q = "select s.id, s.course_id, s.name as sectionName,s.semester_id as semesterId, \n"
                + "s.semester_year as semesterYear, c.code as courseCode, c.name as courseName, c.credit_hours as creditHours, \n"
              + "	s.professor_id, u.first_name as profFirstName, u.last_name as profLastName, u.email as profEmail, u.user_name as profUsername \n"
              + "from sections s \n"
              + "join users u on u.id = s.professor_id \n"
              + "join courses c on c.id = s.course_id \n"
              + "order by c.code";
        prep = conn.prepareStatement(q);

        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                allSections.add(this.createSection(rs));
            }
        } catch (Exception e) {
            System.out.println("Error while fetching all sections: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }

        return allSections;
    }

    private Section createSection(ResultSet rs) throws Exception {
        if (rs == null) {
            return null;
        }

        Course course;
        User professor;
        // Section
        int sectionId = rs.getInt("id");
        String sectionName = rs.getString("sectionName");

        // Semester
        int semesterId = rs.getInt("semesterId");
        int semesterYear = rs.getInt("semesterYear");
        SemesterType semesterType = SemesterType.getSemesterTypeById(semesterId);
        // Course
        int courseId = rs.getInt("course_id");
        String courseCode = rs.getString("courseCode");
        String courseName = rs.getString("courseName");
        int creditHours = rs.getInt("creditHours");
        course = new Course(courseId, courseCode, courseName, creditHours);
       
        // Professor
        int profId = rs.getInt("professor_id");
        String profUsername = rs.getString("profUsername");
        String profFirstName = rs.getString("profFirstName");
        String profLastName = rs.getString("profLastName");
        String profEmail = rs.getString("profEmail");
        
        
        professor = new User(profId, profUsername, profFirstName, profLastName, profEmail, Role.PROF);

        //return new Section(sectionId, sectionName, professor, course);
        return new Section(sectionId, sectionName, professor, course, semesterType,semesterYear);
    }
    
    Section getSectionById(int section_id) throws Exception {
        Section section = null;
        String query = "select s.id, s.course_id, s.name as sectionName, s.semester_id as semesterId,"
                + "s.semester_year as semesterYear, c.code as courseCode, c.name as courseName, c.credit_hours as creditHours, \n"
              + "	s.professor_id, u.first_name as profFirstName, u.last_name as profLastName, u.email as profEmail, u.user_name as profUsername \n"
              + "from sections s \n"
              + "join users u on u.id = s.professor_id \n"
              + "join courses c on c.id = s.course_id \n"
              + "where s.id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, section_id);
        
        ResultSet rs = prep.executeQuery();       
   
        try {
            if(rs.next()) { //if the user was found
                if(section_id == rs.getInt("id"))
                    section = (this.createSection(rs));  
            }
        }
        catch (Exception e) {
            System.out.println("getSectionById: Error while creating section: "+e.getMessage());
            throw e;
        }
        finally {
            prep.close();
        }
        return section;
    }    

    boolean deleteSection(int userId, int sectionId) throws Exception {
        Section section = null;
         String query = "delete s from sections s where s.id = ?";
        
        prep = conn.prepareStatement(query);
        prep.setInt(1, sectionId);
        
        try {
            prep.executeUpdate();
            section = getSectionById(sectionId);
        }
        catch (SQLException e) {
            throw e;
        }
        finally {
            prep.close();
        }
        
        return (section == null);
    }
}
